Database

Single-table database

• A database is a structured collection of data that allows people to extract information in a way that meets their needs.

• The data can include text, numbers, pictures; anything that can be stored in a computer.

• Relational databases will be studied at A Level but for IGCSE only single-table databases will be studied.

• A single-table database contains only one table.

Why are databases useful?

• Databases prevent problems occurring because:
• if any changes or additions are made it only has to be done once – data is consistent
• the same data is used by everyone
• data is only stored once in relational databases which means no data duplication.

What are databases used for?

• To store information about people, for instance:

  • patients in a hospital
  • pupils at a school.

• To store information about things, for instance:

  • cars to be sold
  • books in a library.

• To store information about events, for instance:

  • hotel bookings
  • results of races.

Fields and records

TIP

• each record is a row in the table
• each field is a column in the table.

• Inside a database, data is stored in tables, which consists of many records.
• Each record consists of several fields.
• The number of records in a table will vary as new records can be added and deleted from a table as required.
• The number of fields in a table is fixed so each record contains the same number of fields.

• A table contains data about one type of item or person or event, and will be given a meaningful name, for example:

  • a table of patients called PATIENT
  • a table of books called BOOK
  • a table of doctor’s appointments called APPOINTMENT.

• Each record within a table contains data about a single item, person or event, for example:

  • Winnie Sing (a hospital patient)
  • IGCSE Computer Science (a book)
  • 15:45 on January 2020 (an appointment).

• As every record contains the same number of fields, each field in a record contains a specific piece of information about the single item, person or event stored in that record.

• Each field will have a meaningful name to identify the data stored in it, for example:

• For a hospital patient the fields could include:

Validation

• The role of validation was discussed in Section 7.5.

• It may be worth the reader revisiting this part of the book before continuing with this chapter.

• For example, the DateOfAdmission field will automatically be checked by the software to make sure that any data input is a valid date before it can be stored in the PATIENT table.

Basic data types

• Each field will require a data type to be selected. A data type classifies how the data is stored, displayed and the operations that can be performed on the stored value.
• There are six basic data types that you need to be able to use in a database:

Data Type	Description	Example
text/alphanumeric	A number of characters	apple
character	A single character	a
Boolean	either True or False	True
integer	Whole number	10
real	A decimal number	18.0
date/time	Date and/or time	11/11/2022

Primary keys

• As each record within a table contains data about a single item, person, or event, it is important to be able to uniquely identify this item.
• In order to reliably identify an item from the data stored about it in a record there needs to be a field that uniquely identifies the item. This field is called the primary key.
• A field that is a primary key must contain data values that are never repeated in the table.
• For example: ID is the primary key in Student table.

Student

ID	FirstName	LastName	Age	ClassID
1	Jack	Smith	10	1
2	Tom	Bush	11	2
3	Tina	White	11	2

Structured Query Language (SQL)

• Structured Query Language (SQL) is the standard query language for writing scripts to obtain useful information from a database.
• We will be using SQL to obtain information from single-table databases.
• This will provide a basic understanding of how to obtain and display only the information required from a database.
• Only the SELECT and FROM commands are mandatory in an SQL script. All other commands are optional.

SQL scripts

• An SQL script is a list of SQL commands that perform a given task, often stored in a file so the script can be reused.

Online SQL

https://sql.js.org/examples/GUI/

SQL operators

Operator	Description
=	equal to
>	greater than
<	less than
>=	greater than or equal to
<=	less than equal to
<>	not equal to
BETWEEN	between a range of two values
LIKE	search for a pattern
IN	specify multiple values
AND	specify multiple conditions that must all be true
OR	specify multiple conditions where one or more conditions must be true
NOT	specify a condition that must be false

SQL Command

SQL(DML) query command	Description
SELECT FROM	Fetches data from a database. Queries always beginwith SELECT.
WHERE	Includes only rows in a query that match a given condition
ORDER BY	Sorts the results from a query by a given column eitheralphabetically or numerically
SUM	Returns the sum of all the values in the column
COUNT	Counts the number of rows where the column is not NUL

SELECT FROM

SELECT LastName, Age FROM Student;

WHERE

SELECT LastName, Age FROM Student
WHERE Age > 10;

ORDER BY

SELECT LastName, Age FROM Student
WHERE Age > 10
ORDER BY Age;

SUM

SELECT SUM(Age)
FROM Student

COUNT

SELECT COUNT(*)
FROM Student